package com.wootion.dizhi.service;

import com.wootion.dizhi.entity.Cylinder;
import com.wootion.dizhi.exception.BusinessException;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class ExcelUtil {

    /**
     * 原文件名
     */
    private String fileName;

    private Workbook workbook;

    private MultipartFile multipartFile;


    /**
     * 读取输入文件,返回工作流
     * @return
     */
    public Workbook getWorkbook() throws BusinessException, IOException {
        //获得原文件名
        String fileName = multipartFile.getOriginalFilename();
        //判断文件是否是excel文件
        assert fileName != null;
        if(!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){
            throw new BusinessException(fileName + "不是excel文件");
        }
        //获取文件流
        this.workbook = WorkbookFactory.create(multipartFile.getInputStream());
        return workbook;
    }

    /**
     * 获取有值的sheet下标
     * @param
     * @return
     */
    public List<Integer> getEffectiveNum() {
        List<Integer> list = new ArrayList<>();
        //sheet表总数
        int numberOfSheets = workbook.getNumberOfSheets();
        for(int sheetNum = 0; sheetNum < numberOfSheets; sheetNum++){
            //获取当前页的sheet
            Sheet sheet = workbook.getSheetAt(sheetNum);
            //来获取该 sheet 中包含的行数。如果行数为 0，则该 sheet 为空。
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            if(physicalNumberOfRows != 0){
                list.add(sheetNum);
            }
        }
        return list;
    }

    /**
     * 如果解析不到值就使用此方法
     * @return
     */
    public Object getValueStrengthen(Cell cell) {
        String stringCellValue = cell.getStringCellValue();
        cell.setCellType(CellType.STRING);
        if (NumberUtils.isCreatable(stringCellValue)) {
            NumberFormat numberFormat = NumberFormat.getInstance();
            //如果在格式化和分析数字时使用千分隔符，则为true.
            numberFormat.setGroupingUsed(false);
            //x设置小数点保留位数，这里保留10位小数
            numberFormat.setMaximumFractionDigits(10);
            //numberFormat.format()格式化小数
            stringCellValue = numberFormat.format(Double.valueOf(stringCellValue));
        }
        return stringCellValue;
    }


    /**
     * 获取特定sheet中的所有表头
     * @param sheet
     * @return
     */
    public List<String> getBoxHead(Sheet sheet){
        List<String> list = new ArrayList<>();

        //获取表头(也就是第0行)这一行
        Row row0 = sheet.getRow(0);
        //根据表头行，得到其迭代器
        Iterator<Cell> cellIterator = row0.cellIterator();
        //根据表头行的迭代器遍历，拿到每一个表头字段
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            list.add(cell.getStringCellValue());
        }

        return list;
    }

    public ExcelUtil(MultipartFile multipartFile) {
        this.multipartFile = multipartFile;
        this.fileName = multipartFile.getOriginalFilename();
    }
}
